﻿Imports DTO
Imports System.Data.OleDb

Public Class CTPhieuThueDao
    Public Function TimKiem(ByVal ctptCrt As CTPhieuThueCrt) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(ctptCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Private Function BuildQuery(ByVal ctptCrt As CTPhieuThueCrt, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKMaCTPhieuThue = " 1=1 "
        Dim strDKTenKH = " 1=1 "
        Dim strDKLoaiKH = " 1=1 "
        Dim strDiaChi = " 1=1 "
        Dim strDKCMND = " 1=1 "
        Dim strDKMaPhieuThue = " 1=1 "
        If (ctptCrt.MaCTPhieuThue <> 0) Then
            strDKMaCTPhieuThue = " MaChiTietPhieuThue = ? "
            cmd.Parameters.Add("@MaChiTietPhieuThue", OleDbType.Integer)
            cmd.Parameters("@MaChiTietPhieuThue").Value = ctptCrt.MaCTPhieuThue
        End If
        If (ctptCrt.CheckDiaChi And ctptCrt.DiaChi <> "") Then
            strDiaChi = " DiaChi like ? "
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%%" + ctptCrt.DiaChi + "%%"
        End If
        If (ctptCrt.checkCMND And ctptCrt.CMND <> "") Then
            strDKCMND = " CMND like ? "
            cmd.Parameters.Add("@CMND", OleDbType.WChar)
            cmd.Parameters("@CMND").Value = "%" + ctptCrt.CMND + "%"
        End If
        If (ctptCrt.CheckTenKH And ctptCrt.TenKhachHang <> "") Then
            strDKTenKH = "TenKhachHang like ? "
            cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            cmd.Parameters("@TenKhachHang").Value = "%" + ctptCrt.TenKhachHang + "%"
        End If
        
        If (ctptCrt.CheckLoaiKH) Then
            strDKLoaiKH = " MaLoaiKhachHang = ? "
            cmd.Parameters.Add("@MaLoaiKhachHang", OleDbType.Integer)
            cmd.Parameters("@MaLoaiKhachHang").Value = ctptCrt.LoaiKhachHang
        End If
        If (ctptCrt.CheckMaPhieuThue) Then
            strDKMaPhieuThue = " MaPhieuThue = ? "
            cmd.Parameters.Add("@MaPhieuThue", OleDbType.Integer)
            cmd.Parameters("@MaPhieuThue").Value = ctptCrt.MaPhieuThue
        End If
        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaCTPhieuThue
        strDKWhere += " and " + strDKTenKH
        strDKWhere += " and " + strDKLoaiKH
        strDKWhere += " and " + strDiaChi
        strDKWhere += " and " + strDKCMND
        strDKWhere += " and " + strDKMaPhieuThue

        Dim strSQL As String = "Select * From CHITIETTHUEPHONG "
        strSQL += strDKWhere
        strSQL += " Order by MaChiTietPhieuThue "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd
    End Function
End Class
